#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time    : 2023/03/14
# @Author  : Tuut
# @Email   : 49616026@qq.com
# @File    : MySqlConfig.py
import pymysql


#  mysql连接信息
#### 数据库连接并查询
class MySqlConfig:
    host = ''
    user = ''
    passwd = ''
    db = ''
    port = 0

    def __init__(self, host, user, passwd, db, port):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.port = port

    def conn(self):
        conn = pymysql.connect(host=self.host,
                               user=self.user,
                               passwd=self.passwd,
                               db=self.db,
                               port=self.port,
                               charset="utf8",
                               autocommit=True  # 此参数代表自动提交，如不写则需单独执行commit()命令
                               )
        return conn

    def sql_fetch(self, count_sql):
        db = self.conn()
        cursor = db.cursor()  # 获取操作游标
        cursor.execute(count_sql)  # 运行SOL语句
        lists = cursor.fetchall()  # 接收返回的结果
        db.close()  # 关闭连接
        return lists

    def sql_execute(self, execute_sql):
        db = self.conn()
        cursor = db.cursor()  # 获取操作游标
        cursor.execute(execute_sql)  # 运行SOL语句
        data = cursor.fetchall()  # 接收返回的结果
        db.close()  # 关闭连接
        return data

    def sql_execute_batch(self, execute_sql):
        db = self.conn()
        cursor = db.cursor()  # 获取操作游标
        for _sql in execute_sql:
            cursor.execute(execute_sql)  # 运行SOL语句
            cursor.fetchall()  # 接收返回的结果
        db.commit()
        db.close()  # 关闭连接
        return 200

    def sql_commit(self, execute_sql):
        db = self.conn()
        cursor = db.cursor()  # 获取操作游标
        cursor.execute(execute_sql)  # 运行SOL语句
        data = cursor.fetchall()  # 接收返回的结果
        # 最后插入行的主键id
        id_1 = cursor.lastrowid
        # 最新插入行的主键id
        id_2 = db.insert_id()
        db.commit()
        db.close()  # 关闭连接
        return [id_1, id_2]

    def sql_add(self, update_sql):
        db = self.conn()
        cursor = db.cursor()  # 获取操作游标
        cursor.execute(update_sql)  # 运行SOL语句
        lists = cursor.fetchall()  # 接收返回的结果
        db.close()  # 关闭连接
        return lists
